﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using UnityEngine;

namespace Assets._10STest.Scripts
{
    class DBService
    {
        //连接类对象
        private static MySqlConnection mySqlConnection;
        //IP地址
        private static string host;
        //端口号
        private static string port;
        //用户名
        private static string userName;
        //密码
        private static string password;
        //数据库名称
        private static string databaseName;

        //string sqlCon = "server=localhost;user id=root;password=WANGshuai123...;data=userTable";

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="_host">ip地址</param>
        /// <param name="_userName">用户名</param>
        /// <param name="_password">密码</param>
        /// <param name="_databaseName">数据库名称</param>
        public DBService(string _host, string _port, string _userName, string _password, string _databaseName)
        {
            host = _host;
            port = _port;
            userName = _userName;
            password = _password;
            databaseName = _databaseName;
            OpenSql();
        }

        public DBService()
        {
            host = "127.0.0.1";
            port = "3306";
            userName = "root";
            password = "9647";
            databaseName = "leedb";
            OpenSql();
        }

        /// <summary>
        /// 打开数据库
        /// </summary>
        public void OpenSql()
        {
            try
            {
                string mySqlString = string.Format("Database={0};Data Source={1};User Id={2};Password={3};port={4}"
                    , databaseName, host, userName, password, port);
                mySqlConnection = new MySqlConnection(mySqlString);
                mySqlConnection.Open();
            }
            catch (Exception e)
            {
                throw new Exception("服务器连接失败，请重新检查MySql服务是否打开。" + e.Message.ToString());
            }

        }

        /// <summary>
        /// 关闭数据库
        /// </summary>
        public void CloseSql()
        {
            if (mySqlConnection != null)
            {
                mySqlConnection.Close();
                mySqlConnection.Dispose();
                mySqlConnection = null;
            }
        }

        public bool SelectUser(string name,string password)
        {
            string sql = "select * from user where username=@para1 and password=@para2";
            MySqlCommand cmd = new MySqlCommand(sql, mySqlConnection);
            cmd.Parameters.AddWithValue("para1", name);
            cmd.Parameters.AddWithValue("para2", password);

            MySqlDataReader reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                return true;
            }
            return false;
        }

        public bool AddUser(string name,string password)
        {
            try
            {
                string sql = "";
                sql = "insert into user(userName,password) values";
                string values = "('" + name + "','" + password + "')";
                sql += values;
                //string sql = "delete from user where userid='9'";
                //string sql = "update user set username='啊哈',password='123' where userid='8'";
                MySqlCommand cmd = new MySqlCommand(sql, mySqlConnection);
                int result = cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                throw new Exception().GetBaseException();
                return false;
            }
        }

        //public bool SelectUser(string name,string password)
        //{
        //    try
        //    {
        //        string sql = "select * from user where ";
        //        string conditions = string.Format("userName = '{0}' and password = '{1}' ", name,password);
        //        sql += conditions;
        //        if (dataSet(sql) != null)
        //        {
        //            return true;
        //        }
        //        return false;
        //    }
        //    catch (Exception e)
        //    {
        //        throw new Exception().GetBaseException();
        //    }
        //}

        private DataSet dataSet(string sql)
        {
            if (mySqlConnection.State == ConnectionState.Open)
            {
                DataSet ds = new DataSet();
                try
                {
                    MySqlDataAdapter mySqlAdapter = new MySqlDataAdapter(sql, mySqlConnection);
                    mySqlAdapter.Fill(ds);
                }
                catch (Exception e)
                {
                    throw new Exception("SQL:" + sql + "/n" + e.Message.ToString());
                }
                return ds;
            }
            return null;
        }
    }
}
